package com.wangxianseng.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.IndexedColors;

import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;


public class ExcelUtil {

    /**
     * @param fileName 文件名
     * @param headers 表格属性列名数组
     * @param dataset 需要显示的数据集合
     * @throws IOException
     */
    public static HSSFWorkbook exportExcel(HttpServletResponse response, String fileName,
                                           String[] headers, List<Object[]> dataset) throws IOException {
        // 设置请求
        // 防止中文乱码
        fileName = URLEncoder.encode(fileName,"UTF-8");
        response.setCharacterEncoding("utf-8");
        response.addHeader("Content-Disposition", "attchement;filename*=" + fileName +".xlsx");
        // 创建一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet("表单导出");

        //样式
        // 创建表头样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        // 创建一个字体
        HSSFFont headerFont = workbook.createFont();
        headerFont.setFontName("Arial Black");
        headerFont.setFontHeightInPoints((short) 16); // 设置字体大小
        headerFont.setColor(IndexedColors.PALE_BLUE.getIndex()); // 设置字体颜色
        headerStyle.setFont(headerFont); // 将字体应用到样式

        // 设置背景色
//        headerStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
        headerStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
        headerStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());



        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            cell.setCellStyle(headerStyle);
        }
        // 遍历集合数据，产生数据行
        Iterator<Object[]> it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            // 从第1行开始创建
            row = sheet.createRow(index);
            row.setHeight((short) 550);

            Object[] obj = (Object[]) it.next();
            for (short i = 0; i < obj.length; i++) {
                HSSFCell cell = row.createCell(i);
                Object value = obj[i];
                String textValue = null;
                if (!"".equals(value) && value != null) {
                    if (value instanceof Integer) {
                        int intValue = (Integer) value;
                        cell.setCellValue(intValue);
                    } else if (value instanceof Float) {
                        float fValue = (Float) value;
                        cell.setCellValue(fValue);
                    } else if (value instanceof Double) {
                        double dValue = (Double) value;
                        cell.setCellValue(dValue);
                    } else if (value instanceof Long) {
                        long longValue = (Long) value;
                        cell.setCellValue(longValue);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        textValue = value.toString();
                        // 设置单元格的值
                        cell.setCellValue(textValue);
                    }
                } else {
                    cell.setCellValue("");
                }
            }
        }
        // 让列宽随着导出的列长自动适应
        for (int colNum = 0; colNum < headers.length; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                // 当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(colNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(colNum);
                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue() != null
                                ? currentCell.getStringCellValue().getBytes().length : 10;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            if (colNum == 0) {
                sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
            } else {
                sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
            }
        }

        // 打开流
        OutputStream outputStream = response.getOutputStream();
        // HSSFWorkbook写入流
        workbook.write(outputStream);
        // HSSFWorkbook关闭
        workbook.close();
        // 刷新流
        outputStream.flush();
        // 关闭流
        outputStream.close();
        return workbook;
    }
    /**
     * @param fileName 文件名
     * @param headers 表格属性列名数组
     * @param dataset 需要显示的数据集合
     * @throws IOException
     */
    public static HSSFWorkbook exportAvgExcel(HttpServletResponse response, String fileName,
                                           String[] headers, List<Object[]> dataset) throws IOException {
        // 设置请求
        // 防止中文乱码
        fileName = URLEncoder.encode(fileName,"UTF-8");
        response.setCharacterEncoding("utf-8");
        response.addHeader("Content-Disposition", "attchement;filename*=" + fileName +".xlsx");
        // 创建一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet("表单导出");

        //样式
        // 创建表头样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        // 创建一个字体
        HSSFFont headerFont = workbook.createFont();
        headerFont.setFontName("Arial Black");
        headerFont.setFontHeightInPoints((short) 16); // 设置字体大小
        headerFont.setColor(IndexedColors.PALE_BLUE.getIndex()); // 设置字体颜色
        headerStyle.setFont(headerFont); // 将字体应用到样式

        // 设置背景色
//        headerStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
        headerStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
        headerStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());



        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            cell.setCellStyle(headerStyle);
        }
        // 遍历集合数据，产生数据行
        Iterator<Object[]> it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            // 从第1行开始创建
            row = sheet.createRow(index);
            row.setHeight((short) 550);

            Object[] obj = (Object[]) it.next();
            for (short i = 0; i < obj.length; i++) {
                HSSFCell cell = row.createCell(i);
                Object value = obj[i];
                String textValue = null;
                if (!"".equals(value) && value != null) {
                    if (value instanceof Integer) {
                        int intValue = (Integer) value;
                        cell.setCellValue(intValue);
                    } else if (value instanceof Float) {
                        float fValue = (Float) value;
                        cell.setCellValue(fValue);
                    } else if (value instanceof Double) {
                        double dValue = (Double) value;
                        cell.setCellValue(dValue);
                    } else if (value instanceof Long) {
                        long longValue = (Long) value;
                        cell.setCellValue(longValue);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        textValue = value.toString();
                        // 设置单元格的值
                        cell.setCellValue(textValue);
                    }
                } else {
                    cell.setCellValue("");
                }
            }
        }
        // 让列宽随着导出的列长自动适应
        for (int colNum = 0; colNum < headers.length; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                // 当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(colNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(colNum);
                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue() != null
                                ? currentCell.getStringCellValue().getBytes().length : 10;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            if (colNum == 0) {
                sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
            } else {
                sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
            }
        }

        // 打开流
        OutputStream outputStream = response.getOutputStream();
        // HSSFWorkbook写入流
        workbook.write(outputStream);
        // HSSFWorkbook关闭
        workbook.close();
        // 刷新流
        outputStream.flush();
        // 关闭流
        outputStream.close();
        return workbook;
    }


    public static void exportToLocalExcel(String resultFilePath, String fileName, String[] arrString, List<Object[]> dataList) {}
}
